import pymysql
import traceback
import pandas
from pymysql.constants import CLIENT

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL

from app.core.config import exec_log as log
from app.core.config import GOINCEPTION_HOST
from app.core.config import GOINCEPTION_PORT
from app.core.config import GOINCEPTION_USER
from app.core.config import GOINCEPTION_PASSWORD
from app.core.config import GOINCEPTION_CHARSET


def goinception_handle(**kwargs):
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    database_name = kwargs.get("database_name")
    is_backup = kwargs.get("is_backup")
    if is_backup == 0:
        is_backup_str = ''
    else:
        is_backup_str = '--backup=true;'
    if database_name is not None:
        use_database_name_sql = f"use {database_name} ; "
    else:
        use_database_name_sql = ''

    operation = kwargs.get("operation", 1)
    if operation == 1:
        operation_type_string = "--check=1;"
    elif operation == 2:
        operation_type_string = "--execute=1;"
    else:
        log.error("operation 操作参数有误")
        return None
    sql_to_execute = kwargs.get('sql_to_execute')
    if sql_to_execute is None:
        log.error("没有传入SQL执行语句")
        return None
    goinception_sql = '''/*--user={mysql_con_user};--password={mysql_con_psw};--host={mysql_con_host};--port={mysql_con_port};{is_backup_str}{operation_type_string}*/
   inception_magic_start;
   {use_database_name_sql}
   {sql_to_execute}
   inception_magic_commit; 
   '''.format(sql_to_execute=sql_to_execute, mysql_con_user=mysql_con_user, mysql_con_psw=mysql_con_psw,
              mysql_con_host=mysql_con_host, mysql_con_port=mysql_con_port, is_backup_str=is_backup_str,
              operation_type_string=operation_type_string, use_database_name_sql=use_database_name_sql)
    # print(goinception_sql)
    log.info(goinception_sql)
    return mysql_execute(execute_sql=goinception_sql, mysql_con_host=GOINCEPTION_HOST,
                         mysql_con_port=GOINCEPTION_PORT,
                         mysql_con_user=GOINCEPTION_USER, mysql_con_psw=GOINCEPTION_PASSWORD)


def mysql_execute(execute_sql, **kwargs):
    """直接操作数据库"""
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    database = kwargs.get("database")
    config = {
        'host': mysql_con_host,
        'port': mysql_con_port,
        'user': mysql_con_user,
        'password': mysql_con_psw,
        'cursorclass': pymysql.cursors.DictCursor,
        'database': database,
    }
    connection = pymysql.connect(**config)
    cursor = connection.cursor()
    cursor.execute(execute_sql)
    execute_result = cursor.fetchall()
    cursor.close()
    connection.close()
    return execute_result


def mysql_execute_multi_sql(execute_sql, **kwargs):
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    database = kwargs.get("database")
    config = {
        'host': mysql_con_host,
        'port': mysql_con_port,
        'user': mysql_con_user,
        'password': mysql_con_psw,
        'cursorclass': pymysql.cursors.DictCursor,
        'database': database,
        "client_flag": CLIENT.MULTI_STATEMENTS
    }
    connection = pymysql.connect(**config)
    cursor = connection.cursor()
    cursor.execute(execute_sql)
    connection.commit()
    cursor.close()
    connection.close()
    # my_query_str = 'show databases;use random_existing_db;'
    # with pymysql.connect(**config) as cur:
    #     cur.execute(execute_sql)


def mysql_execute_cursor(execute_sql, **kwargs):
    """操作数据库，返回生成器"""
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    database = kwargs.get("database")
    dict_cursor = kwargs.get("dict_cursor", 1)
    if dict_cursor == 1:
        config = {
            'host': mysql_con_host,
            'port': mysql_con_port,
            'user': mysql_con_user,
            'password': mysql_con_psw,
            'cursorclass': pymysql.cursors.DictCursor,
            'database': database,
        }
    else:
        config = {
            'host': mysql_con_host,
            'port': mysql_con_port,
            'user': mysql_con_user,
            'password': mysql_con_psw,
            'database': database,
        }
    connection = pymysql.connect(**config)
    cursor = connection.cursor()
    cursor.execute(execute_sql)
    for row in cursor:
        yield row

    # execute_result = cursor.fetchall()
    cursor.close()
    connection.close()
    # return execute_result


def mysql_execute_sql_list(execute_sql_lists, **kwargs):
    """直接操作数据库"""
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    dict_cursor = kwargs.get("dict_cursor", 1)
    database = kwargs.get("database")
    if dict_cursor == 1:
        config = {
            'host': mysql_con_host,
            'port': mysql_con_port,
            'user': mysql_con_user,
            'password': mysql_con_psw,
            'cursorclass': pymysql.cursors.DictCursor,
            'database': database,
        }
    else:
        config = {
            'host': mysql_con_host,
            'port': mysql_con_port,
            'user': mysql_con_user,
            'password': mysql_con_psw,
            'database': database,
        }
    connection = pymysql.connect(**config)
    cursor = connection.cursor()
    for execute_sql in execute_sql_lists:
        cursor.execute(execute_sql)
    cursor.close()
    connection.close()


def mysql_execute_use_pandas(execute_sql, **kwargs):
    mysql_con_host = kwargs.get("mysql_con_host")
    mysql_con_port = kwargs.get("mysql_con_port")
    mysql_con_user = kwargs.get("mysql_con_user")
    mysql_con_psw = kwargs.get("mysql_con_psw")
    database = kwargs.get("database")
    engine = create_engine(URL(
        drivername="mysql",
        username=mysql_con_user,
        password=mysql_con_psw,
        host=mysql_con_host,
        port=mysql_con_port,
        database=database,
    ))
    conn = engine.connect()

    generator_df = pd.read_sql(sql=execute_sql,  # mysql query
                               con=conn,
                               chunksize=1000)  # size you want to fetch each time
    return generator_df
    # for dataframe in generator_df:
    #     for row in dataframe:
    #         pass  # whatever you want to do
